package ws.jonas.sybosc.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import ws.jonas.common.model.Product;
import ws.jonas.sybosc.gui.LogPanel;
import ws.jonas.sybosc.model.StockTableObject;

/**
 * TODO selectDates might be to slow. select first and last manually instead and depend on ordering?
 * TODO Add javadoc
 */
public abstract class SqlDb {
	private static final String DATE_FORMAT = "yyyy-MM-dd";

	protected PreparedStatement selectAllStoresPs;
	protected PreparedStatement selectDisctinctCitiesPs;
	protected PreparedStatement selectDatesPs;
	protected PreparedStatement selectDistinctCountiesPs;
	protected PreparedStatement selectProductsPs;
	protected PreparedStatement selectProductByIdPs;
	protected PreparedStatement selectStocksPs;

	protected Connection conn;

	/**
	 * Get all Products from database.
	 *
	 * @return
	 * @throws SQLException
	 */
	public List<Product> getProducts() throws SQLException {
		ResultSet selectRs = selectProductsPs.executeQuery();
		List<Product> products = new ArrayList<Product>();
		while (selectRs.next()) {
			int prodnr = selectRs.getInt("prod_nr");
			String volume = selectRs.getString("volume");
			String name = selectRs.getString("name");
			products.add(new Product(prodnr, name, volume));
		}
		return products;
	}

	/**
	 * Get all unique (by name) Products from database.
	 *
	 * Unused atm.
	 *
	 * @return
	 * @throws SQLException
	 */
	public List<Product> getProductsUniqueName() throws SQLException {
		ResultSet selectRs = selectProductsPs.executeQuery();
		Map<String, Product> productsByName = new LinkedHashMap<String, Product>();
		while (selectRs.next()) {
			int prodnr = selectRs.getInt("prod_nr");
			String volume = selectRs.getString("volume");
			String name = selectRs.getString("name");
			Product product = productsByName.get(name);
			if (product == null) {
				product = new Product(prodnr, name, volume);
				productsByName.put(name, product);
			}
		}
		return new ArrayList<Product>(productsByName.values());
	}

	/**
	 * Get both extremes in available dates. Earliest data in index 0 and latest date in index 1.
	 *
	 * @return
	 * @throws SQLException
	 */
	public String[] getDateInterval() throws SQLException {
		ResultSet selectRs = selectDatesPs.executeQuery();
		String[] interval = new String[2];
		SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);
		if (selectRs.next()) {
			interval[0] = formatter.format(selectRs.getTimestamp("insert_timestamp"));
		}
		if (selectRs.last()) {
			interval[1] = formatter.format(selectRs.getTimestamp("insert_timestamp"));
		}
		return interval;
	}

	/**
	 * Get all unique cities.
	 *
	 * @return
	 * @throws SQLException
	 */
	public List<String> getDistinctCities() throws SQLException {
		ResultSet selectRs = selectDisctinctCitiesPs.executeQuery();
		List<String> cities = new ArrayList<String>();
		while (selectRs.next()) {
			cities.add(selectRs.getString("city"));
		}
		return cities;
	}

	/**
	 * Get all unique counties.
	 *
	 * @return
	 * @throws SQLException
	 */
	public List<String> getDistinctCounties() throws SQLException {
		ResultSet selectRs = selectDistinctCountiesPs.executeQuery();
		List<String> counties = new ArrayList<String>();
		while (selectRs.next()) {
			counties.add(selectRs.getString("county"));
		}
		return counties;
	}

	/**
	 * Get all available stocks matching the input.
	 *
	 * Any parameter collection may be null or empty, this is regarded as "wild card", i.e. all stocks are accepted for that filter.
	 *
	 * @param productsByName
	 * @param productsByNr
	 * @param cities
	 * @param counties
	 * @param from
	 * @param to
	 * @return
	 * @throws SQLException
	 */
	public Map<Product, List<StockTableObject>> getStocks(Collection<String> productsByName,
			Collection<Integer> productsByNr, Collection<String> cities, Collection<String> counties, String from, String to) throws SQLException {
		try {
			int index = 0;
			if (from == null || from.equals("") || to == null || to.equals("")) {
				String[] dates = getDateInterval();
				if (from == null || from.equals("")) {
					from = dates[0];
				}
				if (to == null || to.equals("")) {
					to = dates[1];
				}
			}
			SimpleDateFormat dateParser = new SimpleDateFormat(DATE_FORMAT);
			long fromTime = dateParser.parse(from).getTime();
			long toTime = dateParser.parse(to).getTime();

			Set<Integer> validProdIds = getProductIdsFromNrAndName(productsByNr, productsByName);
			Set<Integer> validStoreIds = getStoreIdsFromCitiesAndCounties(cities, counties);

			selectStocksPs.setTimestamp(++index, new Timestamp(fromTime));
			selectStocksPs.setTimestamp(++index, new Timestamp(toTime));
			ResultSet stocksRs = selectStocksPs.executeQuery();
			Map<Product, List<StockTableObject>> stocksByProduct = new HashMap<Product, List<StockTableObject>>();
			Map<Integer, Product> productsById = new HashMap<Integer, Product>();
			while (stocksRs.next()) {
				int prodId = stocksRs.getInt("prod_id");
				int storeId = stocksRs.getInt("store_id");
				if (validProdIds.contains(prodId) && validStoreIds.contains(storeId)) {
					int quantity = stocksRs.getInt("quantity");
					Timestamp insertTime = stocksRs.getTimestamp("insert_timestamp");

					Product prod = getProductFromIdCache(prodId, productsById);
					if (prod != null) {
						List<StockTableObject> stocks = stocksByProduct.get(prod);
						if (stocks == null) {
							stocks = new ArrayList<StockTableObject>();
							stocksByProduct.put(prod, stocks);
						}
						stocks.add(new StockTableObject(prodId, storeId, quantity, insertTime));
					} else {
						LogPanel.getInstance().println(new Exception("ERROR: Unable to find product: prodId=" + prodId + ", storeId=" + storeId));
					}
				}
			}

			//TODO remove
			System.out.println("Total result = #" + stocksByProduct.size());
			for (Map.Entry<Product, List<StockTableObject>> entry : stocksByProduct.entrySet()) {
				System.out.println(entry.getKey().getUniqueName() + " = #" + entry.getValue().size());
			}
			/*
			for (Product p : stocksByProduct.keySet()) {
				System.out.println(p.getUniqueName() + " = #" + stocksByProduct.get(p).size());
			}
			*/
			return stocksByProduct;
		} catch (ParseException e) {
			throw new SQLException(e);
		}
	}

	/**
	 * Get Product from database.
	 *
	 * @param id Id of sought Product
	 * @return Product, null if not found in database
	 * @throws SQLException
	 */
	public Product getProductFromId(int id) throws SQLException {
		int index = 0;
		selectProductByIdPs.setInt(++index, id);
		ResultSet selectRs = selectProductByIdPs.executeQuery();
		if (selectRs.next()) {
			int prodnr = selectRs.getInt("prod_nr");
			String name = selectRs.getString("name");
			String volume = selectRs.getString("volume");
			return new Product(prodnr, name, volume);
		}
		return null;
	}

	/**
	 * Get all product ids from product numbers.
	 *
	 * Will not return duplicates, but may return several ids per product number (multiple volumes).
	 *
	 * {@code productsByProdNr} may be null or empty, this is regarded as "wild card", i.e. all products are accepted.
	 *
	 * @param productsByProdNr All valid product numbers
	 * @return All valid product ids
	 * @throws SQLException
	 */
	private Set<Integer> getProductIdsFromNrAndName(Collection<Integer> productsByProdNr, Collection<String> productsByName) throws SQLException {
		boolean checkNr = productsByProdNr != null && !productsByProdNr.isEmpty();
		boolean checkName = productsByName != null && !productsByName.isEmpty();

		ResultSet selectRs = selectProductsPs.executeQuery();
		Set<Integer> validProducts = new HashSet<Integer>();
		while (selectRs.next()) {
			int prodnr = selectRs.getInt("prod_nr");
			String prodName = selectRs.getString("name"); //TODO: This will break if Product.getUniqueName() is used in JList

			boolean valid = !checkNr && !checkName; //wild card? (ie no prodnrs or names selected)
			if (!valid) {
				valid = checkNr && productsByProdNr.contains(prodnr); //if still not valid, check for selected product nrs
			}
			if (!valid) {
				valid = checkName && productsByName.contains(prodName); //if still not valid, check for selected product names
			}

			if (valid) {
				int id = selectRs.getInt("id");
				validProducts.add(id);
			}
		}
		System.out.println("SqlDb.getProductIdsFromNrAndName(): # = " + validProducts.size()); //TODO remove
		return validProducts;
	}

	/**
	 * Get all store ids according to the parameters.
	 *
	 * {@code cities} or {@code counties} may be null or empty, this is regarded as "wild card", i.e. all cities/counties are accepted.
	 *
	 * @param cities All valid cities
	 * @param counties All valid counties
	 * @return All valid store ids
	 * @throws SQLException
	 */
	private Set<Integer> getStoreIdsFromCitiesAndCounties(Collection<String> cities, Collection<String> counties) throws SQLException {
		boolean checkCities = cities != null && !cities.isEmpty();
		boolean checkCounties = counties != null && !counties.isEmpty();

		ResultSet selectRs = selectAllStoresPs.executeQuery();
		Set<Integer> validStoresIds = new HashSet<Integer>();
		while (selectRs.next()) {
			String city = selectRs.getString("city");
			String county = selectRs.getString("county");

			boolean valid = !checkCities && !checkCounties; //wild card? (ie no cities or counties selected)
			if (!valid) {
				valid = checkCities && cities.contains(city); //if still not valid, check for selected cities
			}
			if (!valid) {
				valid = checkCounties && counties.contains(county); //if still not valid, check for selected counties
			}

			if (valid) {
				int id = selectRs.getInt("id");
				validStoresIds.add(id);
			}
		}
		System.out.println("SqlDb.getStoreIdsFromCitiesCounties(): # = " + validStoresIds.size()); //TODO remove
		return validStoresIds;
	}

	/**
	 * Get Product from product id, possibly cached. Equivalent of calling {@link #getProductFromId(int)} if cache is null.
	 *
	 * @param id Id of sought Product
	 * @param cache Known mapping between product ids and Products
	 * @return Sought Product, null if it was not in cache or database
	 * @throws SQLException
	 */
	private Product getProductFromIdCache(int id, Map<Integer, Product> cache) throws SQLException {
		if (cache == null) {
			return getProductFromId(id);
		}
		Product prod = cache.get(id);
		if (prod == null) {
			prod = getProductFromId(id);
			cache.put(id, prod);
		}
		return prod;
	}


	public void close() throws SQLException {
		conn.close();
	}

	public boolean isClosed() throws SQLException {
		return conn.isClosed();
	}

	//
	// SQL STATEMENTS
	//

	protected String getSelectAllStoresStmt() {
		return "SELECT * FROM stores";
	}

	protected String getSelectDistinctCitiesStmt() {
		return "SELECT DISTINCT city FROM stores ORDER BY city";
	}

	protected String getSelectDatesStmt() {
		return "SELECT insert_timestamp FROM stocks ORDER BY insert_timestamp ASC";
	}

	protected String getSelectDistinctCountiesStmt() {
		return "SELECT DISTINCT county FROM stores ORDER BY county";
	}

	protected String getSelectProductsStmt() {
		return "SELECT * FROM products ORDER BY prod_nr";
	}

	protected String getSelectProductByIdStmt() {
		return "SELECT * FROM products WHERE id = ?";
	}

	protected String getSelectedStocksStmt() {
		return "SELECT * FROM stocks WHERE DATE(insert_timestamp) BETWEEN ? AND ?";
	}

	protected void prepareStatements() throws SQLException {
		selectAllStoresPs = conn.prepareStatement(getSelectAllStoresStmt());
		selectDisctinctCitiesPs = conn.prepareStatement(getSelectDistinctCitiesStmt());
		selectDatesPs = conn.prepareStatement(getSelectDatesStmt());
		selectDistinctCountiesPs = conn.prepareStatement(getSelectDistinctCountiesStmt());
		selectProductsPs = conn.prepareStatement(getSelectProductsStmt());
		selectProductByIdPs = conn.prepareStatement(getSelectProductByIdStmt());
		selectStocksPs = conn.prepareStatement(getSelectedStocksStmt());
	}
}
